package com.atguigu.gmallsuger.mapper;

import com.atguigu.gmallsuger.bean.ProductStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

public interface ProductStatsMapper {
    // 计算某天的总的销售额
    @Select("select " +
        "sum(order_amount) " +
        "from product_stats_2021 " +
        "where toYYYYMMDD(stt)=#{date} ")
    BigDecimal getGMV(int date);  // 20210809
    
    // 按照品类分组
    @Select(
        "select   " +
            " tm_id,  " +
            " tm_name,   " +
            " sum(order_amount) order_amount " +
            "from product_stats_2021 " +
            "where toYYYYMMDD(stt)=#{date} " +
            "group by tm_id, tm_name " +
            "order by order_amount desc " +
            "limit 5"
    )
    List<ProductStats> getProductStatsByTM(int date);
    
    // 按照品牌
    @Select(
        "select   " +
            " spu_id,  " +
            " spu_name,   " +
            " sum(order_amount) order_amount " +
            "from product_stats_2021 " +
            "where toYYYYMMDD(stt)=#{date} " +
            "group by spu_id, spu_name " +
            "order by order_amount desc " +
            "limit 5"
    )
    List<ProductStats> getProductStatsBySpu(int date);
    
    
    // 按照品牌
    @Select(
        "select   " +
            " category3_id,  " +
            " category3_name,   " +
            " sum(order_amount) order_amount," +
            " sum(order_ct) order_ct " +
            "from product_stats_2021 " +
            "where toYYYYMMDD(stt)=#{date} " +
            "group by category3_id, category3_name " +
            "order by order_amount desc " +
            "limit #{limit}"
    )
    List<ProductStats> getProductStatsByC3(@Param("date") int date,
                                            @Param("limit") int limit);
    
}
